


ALTER              proc sp_create_update_Class_store_lot_cavium (
 @part_number varchar(50), 
 @lot_number varchar(50), 
 @qty_in int,
 @qty_out int,
 @date_in datetime,
 @date_out datetime,
 @inventory_type varchar(50),
 @vendor_name varchar(50),
 @date_code varchar(50),
 @vendor_lot varchar(50),
 @lot_comments varchar(1000))
 as
 begin
 	declare @process_step_id int
 	declare @location_id int
 	declare @int int
 	declare @normal_cost float
 	declare @lot_id int
 	declare @this_lot_status varchar(50)
 	-- modified by sahoo as added @vendor_id on 21st April 2004
 	declare @vendor_id int
 
     	-- Commented by KumaraSwamy on 20th August, 2004.
 	/*if ( ( @vendor_lot is null ) OR ( @vendor_lot = '' ) )
 	begin
 		set @vendor_lot = @lot_number 
         end*/
 	
 	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name like @inventory_type and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
 	print @process_step_id
 	
 	-- modified by sahoo on 21st April 2004
 	if @process_step_id is null -- new Product or Product Name mismatch
 
 	begin
 		select @vendor_id = vendor_id from vendor where v_name = @vendor_name
 		select @location_id = location_id from location where vendor_id = @vendor_id and l_type = @inventory_type
 		select @process_step_id = processstep_id from product_map_table where location_id = @location_id		
 		if @process_step_id is null
 		begin
 			-- dummy update to avoid SQLException
 			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
 			return
 		end
 	end
 
 	select @location_id = Location_ID from Location where L_Type like @inventory_type and L_Vendor_Name = @vendor_name
 	print @location_id
 	select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
 	print @lot_id
 	if @lot_id is null -- Check if the lot exists
 	begin -- Create Lot
 		select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
 		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp, Lot_Date_Code, Lot_Comments) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @qty_in), 'Running', CURRENT_TIMESTAMP, @date_code, @lot_comments)
 		select @lot_id = @@IDENTITY
 	end
 	
 	else
 	begin
 		update Lot_Table set Lot_Current_Status = 'Running', Lot_Date_Code = @date_code, Lot_Comments = @lot_comments where Lot_ID = @lot_id
 	end	
 
 	declare @inv_lot_id int
 	select @inv_lot_id = Lot_ID from Inventory_Lot where lot_id = @lot_id
 	if @inv_lot_id is null
 	begin
 		declare @temp_qty_out int
 		if @qty_out is not null 
 		begin
 			set @temp_qty_out = @qty_out
 		end
 		else
 		begin
 			set @temp_qty_out = 0
 		end
 		--Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, timestamp, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot)
 		if @qty_in = @qty_out
 		begin
			Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, timestamp, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot)
 			update lot_table set lot_current_status ='Close'  where lot_id = @lot_id
 			insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
 			values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Close', @vendor_lot)
 		end
 		else
 		begin
 			
 			if @qty_out is not null and @qty_out > 0
 			begin
			Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, timestamp, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, null, CURRENT_TIMESTAMP, @vendor_lot)
 			insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
 			values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Running', @vendor_lot)
 			end
			else
			begin
				Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, TIMESTAMP, IL_Vendor_Lot_Number) values (@lot_id, @qty_in , null, @date_in, null, CURRENT_TIMESTAMP, @vendor_lot)
			end
 
 			update lot_table set lot_current_status ='Running'  where lot_id = @lot_id
 		end
 	end
 	else
 	begin
 		if @date_out is not null-- update the lot where the qty_out and date_out vals are given
 		begin
 			declare @max_timestamp datetime
  			--update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in - @qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  			if @qty_in = @qty_out
  			begin
				update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in - @qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
				select @max_timestamp = MAX(Timestamp) from Inventory_Lot_History where Lot_ID = @inv_lot_id
				UPDATE Inventory_Lot_History SET IL_Packing_Method = null, IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in, IL_Qty_Out = @qty_out, IL_Qty_Shipped = null, IL_Qty_Balance = null, timestamp = CURRENT_TIMESTAMP, IL_Current_Status = 'Close', IL_Vendor_Lot_Number = @vendor_lot where (Lot_ID = @lot_id ) AND ( Timestamp = @max_timestamp)
  				--insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
  				--values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Close', @vendor_lot)
  				update lot_table set lot_current_status = 'Close' where lot_id = @lot_id
  			end
  			else
  			begin
				update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = null, IL_Qty_In = @qty_in - @qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  				--declare @max_timestamp datetime
  				select @max_timestamp = MAX(Timestamp) from Inventory_Lot_History where Lot_ID = @inv_lot_id and IL_Qty_In = @qty_in
  				update Inventory_Lot_History set IL_Qty_Out = @qty_out, IL_Date_Out = @date_out where (Lot_ID = @inv_lot_id ) AND ( Timestamp = @max_timestamp)
  			end
 
 
 		end
 		else
 		begin
 
 			update lot_table set lot_current_status = 'Running' where lot_id = @lot_id
 			update Inventory_Lot set IL_Date_In = @date_in, IL_Qty_In = @qty_in, IL_Date_Out = null, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
 		end
 	end
 end




